unit Class_Test;

interface
uses
  Classes,SysUtils,ADODB;
type TTest=class
  private
    FTest_id : Integer;
    FTest_Name : string;
    FTest_Time : TDateTime;
    FTest_code :  Integer;
  published
    property Test_id : Integer read FTest_id write FTest_id;
    property Test_Name : string read FTest_Name write FTest_Name;
    property Test_Time : TDateTime read FTest_Time write FTest_Time;
    property Test_code : Integer read FTest_code write FTest_code;
  public
    function  GetNextTestID(aADOConnetion:TADOConnection):Integer;
    function  SelectTestIDByTestCode(aADOConnetion:TADOConnection):Integer;
    procedure Insert(aADOConnetion:TADOConnection);
    procedure update(aADOConnetion:TADOConnection);
    procedure delete(aADOConnetion:TADOConnection);
    class procedure select(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
  public
    destructor Destroy; override;
    constructor Create;
  end;

implementation

{ TTest }

constructor TTest.Create;
begin

end;

procedure TTest.delete(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;
    with cADOC do
    begin
      cSQL := 'DELETE tbl_Test where test_code=:test_code ';
      CommandText := cSQL;

      Parameters.ParamByName('test_code').Value := self.test_code;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

destructor TTest.Destroy;
begin

  inherited;
end;

function TTest.GetNextTestID(aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select max(Test_id)+1 as max_id from tbl_Test';
    cADOD.Open;

    Result := cADOD.FieldByName('max_id').AsInteger;

    if Result = 0 then Result := 1;
  finally
    FreeAndNil(cADOD);
  end;
end;

procedure TTest.Insert(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

    with cADOC do
    begin
      cSQL := 'insert into tbl_test (Test_code,Test_id,Test_name,Test_time) values (:Test_code,:Test_id,:Test_name,:Test_time)';
      CommandText := cSQL;
      Parameters.ParamByName('Test_id').Value := self.Test_id;
      Parameters.ParamByName('Test_name').Value := self.Test_name;
      Parameters.ParamByName('Test_time').Value := self.Test_time;
      Parameters.ParamByName('Test_code').Value := self.Test_code;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

class procedure TTest.select(aADOConnetion: TADOConnection;
  var aList: TStringList; aSQL: string);
var
  cTest:TTest;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_Test ';
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      cTest := TTest.Create;
      aList.AddObject('',cTest);

      cTest.Test_id := ADOD.FieldByName('Test_ID').AsInteger;
      cTest.Test_Name := ADOD.FieldByName('Test_NAME').AsString;
      cTest.Test_Time := ADOD.FieldByName('Test_Time').AsDateTime;
      cTest.Test_code := ADOD.FieldByName('Test_code').AsInteger;

      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;
  
end;

function TTest.SelectTestIDByTestCode(
  aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select Test_id from tbl_Test where Test_code=:Test_code';
    with cADOD.Parameters do
    begin
      ParamByName('Test_code').Value := self.Test_code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('Test_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

procedure TTest.update(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

  with cADOC do
  begin
    cSQL := 'update tbl_Test set Test_time=:Test_time,Test_name=:Test_name where Test_code=:Test_code';
    CommandText := cSQL;
    Parameters.ParamByName('Test_code').Value := self.Test_code;
    Parameters.ParamByName('Test_name').Value := self.Test_name;
    Parameters.ParamByName('Test_time').Value := self.Test_time;
  end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;


end.
